package com.zhongyi.file;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Timestamp;

import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zhongyi.db.impl.DBManagerImpl;
import com.zhongyi.entity.Company;
import com.zhongyi.entity.Contact;
import com.zhongyi.util.ImportUtil;
import com.zhongyi.util.UUIDGenerator;

public class TestReader {
	private static final String filePath = "K:\\中易运维\\信息采集\\201204";
	
	private static Logger logger = Logger.getLogger(TestReader.class);
	
	public static void read(String fileName){
		InputStream is = null;
		Workbook wkbook = null;
		Sheet sheet = null;
		Row row = null;
		Company corp = null;
		Contact contact = null;
		String insetCompany = "insert into td_company(id,corp_name,area_id,address,origin_site,home_page,corp_level,corp_type,linker,keyword,create_time,remark) values (?,?,?,?,?,?,?,?,?,?,?,?)";
		String insertContact = "insert into td_contact(id,corp_id,contact_info,info_type,remark,create_time) values (?,?,?,?,?,?)";
		logger.info("begin to execute : " + fileName);
		try {
			
			is = new FileInputStream(fileName);
			wkbook = new XSSFWorkbook(is);
			sheet = wkbook.getSheetAt(0);
			int rowCount = sheet.getLastRowNum();
			
			for (int i = 1; i <= rowCount; i++) {
				corp = new Company();
				row = sheet.getRow(i);
				//判断当前行是否为空行
				if(ImportUtil.isRowNull(row)){
					break;
				}
				String corpId = UUIDGenerator.create();
				//company message
				corp.setId(corpId);
				corp.setCorp_type(ImportUtil.getCellValue(row.getCell(1)));
				corp.setKeyword(ImportUtil.getCellValue(row.getCell(2)));
				corp.setCorp_name(ImportUtil.getCellValue(row.getCell(4)));
				corp.setLinker(ImportUtil.getCellValue(row.getCell(5)));
				corp.setOrigin_site(ImportUtil.getCellValue(row.getCell(10)));
				corp.setCreate_time(new Timestamp(System.currentTimeMillis()));
				corp.setRemark(ImportUtil.getCellValue(row.getCell(11)));
				insertCompany(insetCompany,corp);
				//Email
				String value = ImportUtil.getCellValue(row.getCell(6));
				String[] vs = null;
				if(value != null && value.length() > 0){
					vs = value.split("\\|");
					for(String v : vs){
						contact = new Contact();
						contact.setId(UUIDGenerator.create());
						contact.setCorp_id(corpId);
						contact.setContact_info(v.trim());
						contact.setInfo_type("E");
						contact.setCreate_time(new Timestamp(System.currentTimeMillis()));
						insertContact(insertContact,contact);
					}
				}
				//phone
				value = ImportUtil.getCellValue(row.getCell(7));
				if(value != null && value.length() > 0){
					vs = value.split("\\|");
					for(String v : vs){
						contact = new Contact();
						contact.setId(UUIDGenerator.create());
						contact.setCorp_id(corpId);
						contact.setContact_info(v.trim());
						contact.setInfo_type("P");
						contact.setCreate_time(new Timestamp(System.currentTimeMillis()));
						insertContact(insertContact,contact);
					}
				}
				//mobile
				value = ImportUtil.getCellValue(row.getCell(8));
				if(value != null && value.length() > 0){
					vs = value.split("\\|");
					for(String v : vs){
						contact = new Contact();
						contact.setId(UUIDGenerator.create());
						contact.setCorp_id(corpId);
						contact.setContact_info(v.trim());
						contact.setInfo_type("M");
						contact.setCreate_time(new Timestamp(System.currentTimeMillis()));
						insertContact(insertContact,contact);
					}
				}
				//Fax
				value = ImportUtil.getCellValue(row.getCell(9));
				if(value != null && value.length() > 0){
					vs = value.split("\\|");
					for(String v : vs){
						contact = new Contact();
						contact.setId(UUIDGenerator.create());
						contact.setCorp_id(corpId);
						contact.setContact_info(v.trim());
						contact.setInfo_type("F");
						contact.setCreate_time(new Timestamp(System.currentTimeMillis()));
						insertContact(insertContact,contact);
					}
				}
			}
		} catch (FileNotFoundException e) {
			logger.error("can not find the source file ... ", e);
		} catch (IOException e) {
			logger.error("error to load the file by poi ... ", e);
		}
		logger.info("finish ... ");
	}


	public static void main(String[] args) {
		File dirFile = new File(filePath);
		String basePath = dirFile.getAbsolutePath();
		if(dirFile.isDirectory()){
			String[] files = dirFile.list();
			for(String file : files){
				read(basePath + File.separator + file);
			}
		}else{
			read(basePath);
		}
		
	}
	
	public static void insertCompany(String sql,Company corp){
		DBManagerImpl dbManger = new DBManagerImpl();
		//id,corp_name,area_id,address,origin_site,home_page,corp_level,corp_type,linker,keyword,create_time,remark
		dbManger.insertToDB(sql, new Object[]{corp.getId(),corp.getCorp_name(),corp.getArea_id(),corp.getAdress(),corp.getOrigin_site(),corp.getHome_page(),corp.getCorp_level(),corp.getCorp_type(),corp.getLinker(),corp.getKeyword(),corp.getCreate_time(),corp.getRemark()});
	}
	
	public static void insertContact(String sql , Contact contact){
		DBManagerImpl dbManger = new DBManagerImpl();
		//id,corp_id,contact_info,info_type,remark,create_time
		dbManger.insertToDB(sql, new Object[]{contact.getId(),contact.getCorp_id(),contact.getContact_info(),contact.getInfo_type(),contact.getRemark(),contact.getCreate_time()});
	}

}
